package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import cn.edu.sdjzu.xg.bysj.service.DegreeService;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import cn.edu.sdjzu.xg.bysj.service.ProfTitleService;
import util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;

public final class TeacherDao {
	private static TeacherDao teacherDao=new TeacherDao();
	private TeacherDao(){}
	public static TeacherDao getInstance(){
		return teacherDao;
	}


	public Collection<Teacher> findAll() throws SQLException {
		Collection<Teacher> teachers = new HashSet<>();
		Connection connection = JdbcHelper.getConn();
		Statement stmt = connection.createStatement();
		ResultSet resultSet = stmt.executeQuery("select * from teacher");
		//从数据库中取出数据
		while (resultSet.next()){
			teachers.add(new Teacher(
					resultSet.getInt("id"),
					resultSet.getString("name"),
					ProfTitleService.getInstance().find(resultSet.getInt("proftitle_id")),
					DegreeService.getInstance().find(resultSet.getInt("degree_id")),
					DepartmentService.getInstance().find(resultSet.getInt("department_id"))
			));
		}
		JdbcHelper.close(stmt,connection);
		return teachers;
	}
	
	public Teacher find(Integer id) throws SQLException {
		Connection connection = JdbcHelper.getConn();
		String findTeacher_sql = "select * from teacher where id=?";
		PreparedStatement pstmt = connection.prepareStatement(findTeacher_sql);
		pstmt.setInt(1,id);
		ResultSet resultSet = pstmt.executeQuery();
		resultSet.next();
		Teacher teacher = new Teacher(
				resultSet.getInt("id"),
				resultSet.getString("name"),
				ProfTitleService.getInstance().find(resultSet.getInt("proftitle_id")),
				DegreeService.getInstance().find(resultSet.getInt("degree_id")),
				DepartmentService.getInstance().find(resultSet.getInt("department_id"))

		);
		return teacher;
	}
	
	public boolean update(Teacher teacher) throws SQLException {
		Connection connection = JdbcHelper.getConn();
		String updateTeacher_sql = "update teacher set name=?,proftitle_id=?,degree_id=?,department_id=? where id=?";
		PreparedStatement preparedStatement = connection.prepareStatement(updateTeacher_sql);
		preparedStatement.setString(1,teacher.getName());
		preparedStatement.setInt(2,teacher.getTitle().getId());
		preparedStatement.setInt(3,teacher.getDegree().getId());
		preparedStatement.setInt(4,teacher.getDepartment().getId());
		preparedStatement.setInt(5,teacher.getId());
		int affectedRowNum = preparedStatement.executeUpdate();
		return affectedRowNum>0;
	}
	
	public boolean add(Teacher teacher) throws SQLException {
		//获得连接对象
		Connection connection = JdbcHelper.getConn();
		//创建sql语句
		String addTeacher_sql = "insert into teacher (name, proftitle_id, degree_id, department_id) values (?,?,?,?)";
		PreparedStatement pstmt = connection.prepareStatement(addTeacher_sql);
		//为预编译赋值
		pstmt.setString(1,teacher.getName());
		pstmt.setInt(2,teacher.getTitle().getId());
		pstmt.setInt(3,teacher.getDegree().getId());
		pstmt.setInt(4,teacher.getDepartment().getId());
		//获取添加记录的行数
		int affectedRowNum = pstmt.executeUpdate();
		System.out.println("添加了"+affectedRowNum+"条记录");
		return affectedRowNum>0;
	}

	public boolean delete(Integer id) throws SQLException {
		Connection connection = JdbcHelper.getConn();
		//删除数据
		String deleteTeacher_sql = "delete from teacher where id=?";
		PreparedStatement pstmt = connection.prepareStatement(deleteTeacher_sql);
		//为预编译参数赋值
		pstmt.setInt(1,id);
		//执行预编译对象的executeUpdate方法,获取删除记录的行数
		int affectedRowNum = pstmt.executeUpdate();
		System.out.println("删除了"+affectedRowNum+"条记录");
		return affectedRowNum>0;
	}
	
}
